package com.psedu.base.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.ResourceUtils;

import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;

public class POIUtils {

    /**
     * 拿到不同类型单元格中的值
     * 1. 字符串: 字符串
     * 2. 布尔: toString
     * 3. 数值(double): 格式化后的字符串
     * @param cell 获取的单元格
     * @return 单元格中的值
     */
    public static String getCellValue(Cell cell) {
        String resultValue = "";
        // 判空
        if (Objects.isNull(cell)) {
            return resultValue;
        }

        // 拿到单元格类型
        int cellType = cell.getCellType();
        switch (cellType) {
            // 字符串类型
            case Cell.CELL_TYPE_STRING:
                resultValue = StringUtils.isEmpty(cell.getStringCellValue()) ? "" : cell.getStringCellValue().trim();
                break;
            // 布尔类型
            case Cell.CELL_TYPE_BOOLEAN:
                resultValue = String.valueOf(cell.getBooleanCellValue());
                break;
            // 数值类型
            case Cell.CELL_TYPE_NUMERIC:
                resultValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                break;
            // 取空串
            default:
                break;
        }
        return resultValue;
    }

    /**
     * 载入excel
     * @param filePath 文件路径
     * @return workbook
     */
    public static HSSFWorkbook loadHSSFWorkbook(String filePath){
        File file = new File(filePath);
        return loadHSSFWorkbook(file);
    }

    public static HSSFWorkbook loadHSSFWorkbook(File file) {
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(file);
            BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
            POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
            return new HSSFWorkbook(fileSystem);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 通过classpath加载文件
     * @param classPath resources里的文件路径
     * @return workbook
     */
    public static HSSFWorkbook loadHSSFWorkbookByResourcePath(String classPath) {
        try {
            ClassPathResource resource = new ClassPathResource(classPath);
            InputStream inputStream = resource.getInputStream();
            BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
            POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
            return new HSSFWorkbook(fileSystem);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void cloneSheet(File excelFile, String srcSheetName, String destSheetName) {
        Workbook sheets = readExcelFromFile(excelFile);
        int index = sheets.getSheetIndex(srcSheetName);
        cloneSheet(excelFile, index, destSheetName);
    }

    /**
     * 克隆num个首页
     * @param workbook 文档
     * @param num 克隆的数量
     */
    public static List<String> cloneSheet(HSSFWorkbook workbook, int num) {
        while(workbook.getNumberOfSheets() > 1) {
            workbook.removeSheetAt(1);
        }
        List<String> sheetNameList = new ArrayList<>();
        String sheetName = workbook.getSheetName(0);
        sheetNameList.add(sheetName);
        for (int i = 0; i < num; i++) {
            HSSFSheet hssfSheet = workbook.cloneSheet(0);
            sheetNameList.add(hssfSheet.getSheetName());
        }
        return sheetNameList;
    }

    public static void cloneSheet(File excelFile, Integer index, String destSheetName) {
        Workbook sheets = readExcelFromFile(excelFile);
        //克隆一个新的sheet
        Sheet newSheet = sheets.cloneSheet(index);
        int sheetIndex = sheets.getSheetIndex(newSheet);
        sheets.setSheetName(sheetIndex, destSheetName);
        try {
            FileOutputStream out = new FileOutputStream(excelFile);
            out.flush();
            sheets.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Workbook readExcelFromFile(File file) {
        if (file == null) {
            return null;
        }
        try {
            return new XSSFWorkbook(new FileInputStream(file));
        } catch (IOException e) {
            throw new RuntimeException("文件解析失败");
        }
    }
}
